#!/usr/bin/python
# -*- coding: UTF-8 -*-
# 文件名：04dic.py

# 字典中的字符串一般都有''单引号
dic1 = {}

# 键不可变，只能是数字,字符串，元组
dic1[1] = 11
dic1['a'] = "aa"

print "打印字典"
print dic1

# Unicode字符串，u'' basestring子类
dic2 = {'VERSION()': u'5.6.40-log'}
print dic2


print "打印字典中的键为a的值"
# 键值区分大小写
print dic1['a']

# 输出所有键，注意后面的括号

print dic1.keys()

# 输出所有值，注意后面的括号
print dic1.values()

# 重点
print "以列表的形式返回元组数组"
print dic1.items()

for k, v in dic1.items():
    print '键为{0}.值为{1}'.format(k, v)

# 键可以是字符串，不加引号就是变量了
dic2 = {2: '22', 3: 'aaa', 'bb': dic1}

print dic2

# 修改字典中的值
dic2[3] = 33

print "修改后的字典信息{0} " .format(dic2)

print "字典的长度{0} " .format(len(dic2))
print "判断字典中是否含有bb {0} " .format(dic2.has_key('bbb'))

# get前面的参数是索引 后面的是找不到索引时的输出
print "返回指定键的值 {0} " .format(dic2.get(22, "没找到"))

# 删除指定的键
bbcontent = dic2.pop("bb")
print '要删除bb键的内容是{0}'.format(bbcontent)

print dic2

dic2[1] = 22

print '把dic2添加到dic1中,dic2会覆盖dic1中的内容 ，没有+号运算符'
dic1.update(dic2)

print dic1

print "\n \n"

sql = ""

sql = sql + 'CREATE TABLE IF NOT EXISTS {0} ( \n'

sql_mid = '`id` bigint(11) NOT NULL AUTO_INCREMENT,\n'
attrs = {'name': 'varchar(200) DEFAULT NULL', 'age': 'int(11) DEFAULT NULL'}
for attr, value in attrs.items():
    sql_mid = sql_mid + '`' + attr + '`' + ' ' + value + ',\n'
sql = sql + sql_mid


constraint = 'PRIMARY KEY(`id`)'
sql = sql + constraint
sql_mid = sql + ') ENGINE=InnoDB DEFAULT CHARSET=utf8'
print sql


#params = {"name": "caixinglong", "age": "38", "income": 1000, }
params = {"name": "caixinglong2", "age": "11", "income": 3300, "sex": 'm', }

key = []
value = []
for tmpkey, tmpvalue in params.items():
    key.append(tmpkey)
    if isinstance(tmpvalue, str):
        value.append("'" + tmpvalue + "'")
    else:
        value.append(str(tmpvalue))

print key
print value


attrs_sql = '(' + ','.join(key) + ')'
print attrs_sql

values_sql = ' values(' + ','.join(value) + ')'

print values_sql
sql = 'insert into 11'
sql = sql + attrs_sql + values_sql
print '_insert:'+sql


#####################
consql = ' '
cond_dict = {"id": '=1', "income": '<4000'}

if cond_dict != '':
    for k, v in cond_dict.items():
        consql = consql + k + v + ' and '
# 查询条件中 加上1=1 方便判断

consql = consql + ' 1=1; '

print '查询条件是', consql
tablename = 'test_mysqldb'

order = ''

# 查询列
fields='*'
if fields == "*":
    sql = 'select * from %s where '
else:
    if isinstance(fields, list):
        fields = ",".join(fields)
        sql = 'select %s from %s where ' % (fields, tablename)
    else:
        raise "fields input error, please input list fields."
    

sql = sql + consql + order
print '最终的select:' + sql


cond_dict = {"name": "caixinglong2", "age": "11", "income": 3300, "sex": 'm', }

 #consql = ' '
#if cond_dict!='':
    #for k, v in cond_dict.items():
        #if isinstance(v, str):
            #v = "\'" + v + "\'"
        #consql = consql + tablename + "." + k + '=' + v + ' and '
#consql = consql + ' 1 = 1

#print "第一种办法： " + consql

#sql = "DELETE FROM %s where%s" % (tablename, consql)
#print sql

#consql = ' '
#if cond_dict != '':
    #for k, v in cond_dict.items():
        #consql = consql + k + v + ' and '
#consql = consql + ' 1=1 '

#print "第二种办法： " + consql

attrs_dict = {"name": "caixinglong", "age": "38"}
cond_dict = {"name": "liuqiao", "age": "18"}
    
attrs_list = []
consql = ' '
for tmpkey, tmpvalue in attrs_dict.items():
    attrs_list.append("`" + tmpkey + "`" + "=" + "\'" + tmpvalue + "\'")
attrs_sql = ",".join(attrs_list)
print "attrs_sql:", attrs_sql
#  `age`='38',`name`='caixinglong'

if cond_dict != '':
    for k, v in cond_dict.items():
        if isinstance(v, str):
            v = "\'" + v + "\'"
        consql = consql + "`" + tablename + "`." + "`" + k + "`" + '=' + v + ' and '
        
consql = consql + ' 1=1 '
sql = "UPDATE %s SET %s where%s" % (tablename, attrs_sql, consql)
print sql
